#!/usr/bin/env python
#coding utf-8

import sqlite3
import MySQLdb
import include.common as common

def createdb():
	db2 = sqlite3.connect(common.PATH+'/searchdb.db3')
	db2.text_factory = str
	c = db2.cursor()
	try:
		c.execute('drop table searchdb;')
		db2.commit()
	except Exception as what:
		print what
	try:
		c.execute('vacuum;')
		db2.commit()
	except Exception as what:
		print what
	try:
		c.execute('''CREATE TABLE searchdb(
  id integer primary key,
  title text,
  date integer,
  nickname text,
  abstract text,
  category text,
  clicks integer,
  comments integer, 
  subs integer, 
  osite text,
  uid integer,
  aliasid integer,
  url text
);''')
		db2.commit()
	except Exception as what:
		print what

def makeidx():
	db2 = sqlite3.connect(common.PATH+'/searchdb.db3')
	db2.text_factory = str
	c = db2.cursor()
	try:
		c.execute('CREATE INDEX idx1 on searchdb (clicks desc);')
		c.execute('CREATE INDEX idx2 on searchdb (comments desc);')
		c.execute('CREATE INDEX idx3 on searchdb (subs desc);')
		c.execute('CREATE INDEX idx4 on searchdb (category);')
		c.execute('CREATE INDEX idx5 on searchdb (date,category);')
		c.execute('vacuum;')
		db2.commit()
	except Exception as what:
		print what

def sync(days='all'):
	if days=='all':
		createdb()
	db1 = common.getdb()
	db2 = sqlite3.connect(common.PATH+'/searchdb.db3')
	db2.text_factory = str
	try:
		days = int(days)
	except:
		days = 'all'
	c1 = db1.cursor()
	if days =='all':
   		c1.execute( '''SELECT id,title,ac_threads.date,nickname,abstract,category,
						clicks+avclicks,comments+avcomments,ac_stats.subs,osite,ac_upers.uid,aliasid,url
                   FROM (ac_threads LEFT JOIN ac_stats USING (id)) LEFT JOIN ac_upers USING (uid)''' );
	else:
   		c1.execute( '''SELECT id,title,ac_threads.date,nickname,abstract,category,
						clicks+avclicks,comments+avcomments,ac_stats.subs,osite,ac_upers.uid,aliasid,url
                   FROM (ac_threads LEFT JOIN ac_stats USING (id)) LEFT JOIN ac_upers USING (uid)
					where ac_threads.date > %s''', (common.last_epoch(days),) );
	c2 = db2.cursor()
	thds = c1.fetchmany(1000)
	while thds:
		c2.executemany('''replace into searchdb 
						(id,title,date,nickname,abstract,category,clicks,comments,subs,osite,uid,aliasid,url)
						values (?,?,?,?,?,?,?,?,?,?,?,?,?)''',thds)
		db2.commit()
		thds = c1.fetchmany(1000)
	if days=='all':
		makeidx()

if __name__ == '__main__':
	import sys
	if len(sys.argv) == 2:
		sync( int(sys.argv[1]) )
	else:
		sync()
	common.clear_cache('all')
	data = open(common.PATH+'/searchdb.db3','rb').read()
	import gzip
	gzip.open(common.PATH+'/static/searchdb.db3.gz','wb').write(data)
